import re

import pymysql.cursors
import requests
from lxml import etree


class New():
    def __init__(self):
        self.url = 'https://www.dzwzzz.com/yanlun/index.html'
        self.headers = {
            'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/75.0.3770.142 Safari/537.36'
        }

    def getLink(self):
        res = self.commonGet(self.url)
        items = res.xpath('//table[@class="booklist"]//tr')
        for item in items:
            title = item.xpath('.//td//a/@title')
            link = item.xpath('.//td//a/@href')
            if len(title) > 0:
                year = title[0][0:4]
                phase = re.findall(r'.*第(.*)期.*', title[0])[0]
                url = 'https://www.dzwzzz.com'+link[0][2:-1]+'l'
                print(url, year, phase)
                self.getList(url, year, phase)

    def getList(self,url, year, phase):
        # 从2014-2019
        res = self.commonGet(url)
        items = res.xpath('//div[@class="blkContainerSblkCon"]//p')
        print('数据')
        print(items)
        if len(items) == 0:
            self.getListYear(url, year, phase)
            return False
        arrs = []
        for index, item in enumerate(items):
            content = item.xpath('./text()')[0].strip()
            if (index+1) % 2 == 0:
                arrs[len(arrs)-1]['author'] = content
                arrs[len(arrs)-1]['year'] = year
                arrs[len(arrs)-1]['phase'] = phase
            else:
                if len(items) % 2 != 0:
                    if index == len(items)-1:
                        print('无需加入')
                    else:
                        arrs.append({
                            'content': content,
                        })
                else:
                    arrs.append({
                        'content': content,
                    })

        print(arrs)

        # 保存到数据库
        print(arrs)
        for index, arr in enumerate(arrs):
            self.saveMysql(arr['year'], arr['phase'],
                           arr['author'], arr['content'], index)

    def getListYear(self, url, year, phase):
        # 2010
        res = self.commonGetGb(url)
        items = res.xpath('//div[@class="article left"]//p')
        arrs = []
        for item in items:
            if len(item) > 0:
                obj = dict()
                obj['content'] = item.xpath('./text()')[0].strip()
                obj['author'] = item.xpath('./text()')[1].strip()
                obj['year'] = year
                obj['phase'] = phase
                arrs.append(obj)
        # 保存到数据库
        print(arrs)
        for index, arr in enumerate(arrs):
            self.saveMysql(arr['year'], arr['phase'],
                           arr['author'], arr['content'], index)

    def commonGet(self, url):
        res = requests.get(url, headers=self.headers)
        res.encoding = "utf8"
        response = etree.HTML(res.text)
        return response

    def commonGetGb(self, url):
        res = requests.get(url, headers=self.headers)
        res.encoding = "gb2312"
        response = etree.HTML(res.text)
        return response

    def saveMysql(self, year, phase, author, content, index):
        connection = pymysql.connect(
            host="localhost",
            user="root",
            password="root",
            db="test",
            charset="utf8mb4")
        try:
            # 获取对话指针
            with connection.cursor() as cursor:
                # 创建mysql 语句
                sql = "insert into `speech`(`year`,`phase`,`author`,`content`,`index`) values(%s,%s,%s,%s,%s)"
                # 执行sql
                cursor.execute(sql, (year, phase, author, content, index))
                # 提交
                connection.commit()
        finally:
            connection.close


if __name__ == "__main__":
    new = New()
    new.getLink()
